package com.wangfugui.mybatisplus.utils;

import com.alibaba.excel.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;

public class ExcelConditionalFormatting {
    public static boolean canConvertToBigDecimal(String str) {
        try {
            new BigDecimal(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
    public static void main(String[] args) {
//        extracted();
    }

    public static void format(String inputFilePath, String outputFilePath) {
        // 输入和输出文件路径
//        String inputFilePath = "/Users/guofeng/Documents/mail/report.xlsx";
//        String outputFilePath = "/Users/guofeng/Documents/mail/output.xlsx";

        try (FileInputStream fis = new FileInputStream(inputFilePath);
             Workbook workbook = WorkbookFactory.create(fis);
             FileOutputStream fos = new FileOutputStream(outputFilePath)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);

            // 创建红色字体样式
            Font redFont = workbook.createFont();
            redFont.setColor(IndexedColors.RED.getIndex());

            CellStyle redStyle = workbook.createCellStyle();
            redStyle.setFont(redFont);
            // 创建带边框的红色字体样式
            CellStyle redWithBorderStyle = createRedFontStyle(workbook);
            // 确定条件列（假设我们要检查"Status"列，位于第4列，索引3）
            int conditionColumnIndex = 2;

            // 遍历所有行（跳过标题行）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 获取条件列单元格
                Cell conditionCell = row.getCell(conditionColumnIndex);
                if (conditionCell == null) continue;
                String firstValue = getCellValueAsString(row.getCell(0));
                // 获取单元格值（根据单元格类型处理）
                String cellValue = getCellValueAsString(conditionCell);
                String preValue = getCellValueAsString(row.getCell(conditionColumnIndex - 1));
                // 检查条件
                if (canConvertToBigDecimal(cellValue)&&new BigDecimal(cellValue).compareTo(BigDecimal.ZERO)<0&& !StringUtils.isBlank(preValue)) {
//                if (conditionValue.equalsIgnoreCase(cellValue)) {
                    // 条件满足，设置整行为红色
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            cell.setCellStyle(redWithBorderStyle);
                        }
                    }


                }else {
                    if (!StringUtils.isBlank(firstValue)&&"Short Exposure空头敞口".equals(firstValue)){
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {

                                cell.setCellStyle(redWithBorderStyle);
                            }
                        }
                    }
                }
                cellValue = getCellValueAsString(row.getCell(conditionColumnIndex+1));
                preValue = getCellValueAsString(row.getCell(conditionColumnIndex-1));
                // 检查条件
                if (canConvertToBigDecimal(cellValue)&&new BigDecimal(cellValue).compareTo(BigDecimal.ZERO)<0&& !StringUtils.isBlank(preValue)) {
//                if (conditionValue.equalsIgnoreCase(cellValue)) {
                    if (!preValue.equals("B")&&!preValue.equals("S")){
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {
                                cell.setCellStyle(redWithBorderStyle);
                            }
                        }
                    }
                    // 条件满足，设置整行为红色

                }
            }

            // 写入输出文件
            workbook.write(fos);
            System.out.println("处理完成，结果已保存到: " + outputFilePath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 创建带黑色边框的红色字体样式
    private static CellStyle createRedFontStyle(Workbook workbook) {
        // 创建字体
        Font font = workbook.createFont();

        // 尝试多种设置颜色的方式
        try {
            // 方式1：使用IndexedColors
            font.setColor(IndexedColors.RED.getIndex());

            // 方式2：如果是XSSFWorkbook，使用RGB值
            if (font instanceof XSSFFont) {
                ((XSSFFont)font).setColor(new XSSFColor(
                        new java.awt.Color(255, 0, 0),
                        new DefaultIndexedColorMap()
                ));
            }

            // 方式3：使用HSSF时设置自定义调色板
            else if (font instanceof HSSFFont) {
                HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette();
                palette.setColorAtIndex(IndexedColors.RED.getIndex(),
                        (byte)255,  // 红色
                        (byte)0,    // 绿色
                        (byte)0     // 蓝色
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 创建样式
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);

        // 设置黑色边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        return style;
    }
    // 辅助方法：将单元格值转换为字符串
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}